
package org.dyndns.cooland.updater.db.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.dyndns.cooland.updater.db.MyBatisConnectionFactory;
import org.dyndns.cooland.updater.db.model.Addon;

public class AddonDAO {
	
	private SqlSessionFactory	sqlSessionFactory;
	
	public AddonDAO () {

		sqlSessionFactory = MyBatisConnectionFactory.getSqlSessionFactory();
	}
	
	public List<Addon> selectAll() {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			List<Addon> list = session.selectList("Addon.selectAll");
			return list;
		}
		finally{
			session.close();
		}
	}
	
	public List<Addon> selectByCategoryId(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			List<Addon> list = session.selectList("Addon.selectByCategoryId",
					id);
			return list;
		}
		finally{
			session.close();
		}
	}
	
	public Addon selectById(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			Addon bean = (Addon) session.selectOne("Addon.selectById", id);
			return bean;
		}
		finally{
			session.close();
		}
	}
	
	public void update(Addon addon) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.update("Addon.update", addon);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	public void insert(Addon addon) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.insert("Addon.insert", addon);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	public void delete(int id) {

		SqlSession session = sqlSessionFactory.openSession();
		try{
			session.delete("Addon.deleteById", id);
			session.commit();
		}
		finally{
			session.close();
		}
	}
	
	// TODO get unique by name, version
	/*
	 * public int add(AddonBean bean) {
	 * 
	 * int newId = -1; try{ connection = getConnection(); } catch (SQLException
	 * e){ e.printStackTrace(); return -1; } // check for existance try{ String
	 * sql = "select id from addons where site_id=? and name=? and version=?";
	 * 
	 * PreparedStatement ptmt = connection.prepareStatement(sql); ptmt.setInt(1,
	 * bean.getSite_id()); ptmt.setString(2, bean.getName()); ptmt.setString(3,
	 * bean.getVersion()); ResultSet res = ptmt.executeQuery();
	 * 
	 * if (res.getRow() > 0){ newId = res.getInt("id"); ptmt.close();
	 * connection.close(); return newId; } } catch (SQLException e){
	 * e.printStackTrace(); return -1; }
	 * 
	 * try{ String sql =
	 * "insert into addons(site_id,category_id,name,version,description,source,size,checksum) values(?,?,?,?,?,?,?,?)"
	 * ; PreparedStatement ptmt = connection.prepareStatement(sql,
	 * PreparedStatement.RETURN_GENERATED_KEYS); ptmt.setInt(1,
	 * bean.getSite_id()); ptmt.setInt(2, bean.getCategory_id());
	 * ptmt.setString(3, bean.getName()); ptmt.setString(4, bean.getVersion());
	 * ptmt.setString(5, bean.getDescription()); ptmt.setString(6,
	 * bean.getSource()); ptmt.setInt(7, bean.getSize()); ptmt.setString(8,
	 * bean.getChecksum()); ptmt.executeUpdate(); // get autogenerated key
	 * ResultSet keyResultSet = ptmt.getGeneratedKeys(); if
	 * (keyResultSet.next()){ newId = (int) keyResultSet.getInt(1); }
	 * ptmt.close(); } catch (SQLException e){ e.printStackTrace(); }
	 * 
	 * // add authors // TODO проверить на уникальность List<AuthorBean> authors
	 * = bean.getAuthors(); Iterator ir = authors.iterator(); while
	 * (ir.hasNext()){ try{ int key = -1; // connection = getConnection();
	 * AuthorBean author = (AuthorBean) ir.next(); // check for existance of
	 * author String sql =
	 * "select id from authors where nickname=? and email=? and web=?";
	 * PreparedStatement ptmt = connection.prepareStatement(sql);
	 * ptmt.setString(1, author.getNickname()); ptmt.setString(2,
	 * author.getEmail()); ptmt.setString(3, author.getWeb()); ResultSet res =
	 * ptmt.executeQuery(); if (res.getRow() > 0){ key = res.getInt("id");
	 * ptmt.close(); } else{ // ptmt.close(); sql =
	 * "insert into authors (nickname, email, web) values (?,?,?)"; ptmt =
	 * connection.prepareStatement(sql,
	 * PreparedStatement.RETURN_GENERATED_KEYS);
	 * 
	 * ptmt.setString(1, author.getNickname()); ptmt.setString(2,
	 * author.getEmail()); ptmt.setString(3, author.getWeb());
	 * ptmt.executeUpdate(); // get autogenerated key
	 * 
	 * ResultSet keyResultSet = ptmt.getGeneratedKeys(); if
	 * (keyResultSet.next()){ key = (int) keyResultSet.getInt(1); }
	 * ptmt.close(); }
	 * 
	 * if (key != -1){ sql =
	 * "insert into addons_authors (addons_id, authors_id) values (?,?)";
	 * connection = getConnection(); ptmt = connection.prepareStatement(sql,
	 * PreparedStatement.RETURN_GENERATED_KEYS); ptmt.setInt(1, newId);
	 * ptmt.setInt(2, key); ptmt.executeUpdate(); ptmt.close(); }
	 * 
	 * } catch (SQLException e){ e.printStackTrace(); }
	 * 
	 * }
	 * 
	 * try{ // if (ptmt != null){ // ptmt.close(); // } if (connection != null){
	 * connection.close(); } } catch (SQLException e){ e.printStackTrace(); }
	 * catch (Exception e){ e.printStackTrace(); }
	 * 
	 * return newId; }
	 * 
	 * public void update(AddonBean bean) {
	 * 
	 * try{ String sql =
	 * "update addons set site_id=?, category_id=?, name=?, version=?, description=?, source=?, size=?, checksum=? where id=?"
	 * ; connection = getConnection(); PreparedStatement ptmt =
	 * connection.prepareStatement(sql); ptmt.setInt(1, bean.getSite_id());
	 * ptmt.setInt(2, bean.getCategory_id()); ptmt.setString(3, bean.getName());
	 * ptmt.setString(4, bean.getVersion()); ptmt.setString(5,
	 * bean.getDescription()); ptmt.setString(6, bean.getSource());
	 * ptmt.setInt(7, bean.getSize()); ptmt.setString(8, bean.getChecksum());
	 * ptmt.setInt(9, bean.getId()); ptmt.executeUpdate(); } catch (SQLException
	 * e){ e.printStackTrace(); } finally{ try{ // if (ptmt != null){ //
	 * ptmt.close(); // } if (connection != null){ connection.close(); } } catch
	 * (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } }
	 * 
	 * public void delete(AddonBean bean) {
	 * 
	 * try{ String sql = "delete from addons where id=?"; connection =
	 * getConnection(); PreparedStatement ptmt =
	 * connection.prepareStatement(sql); ptmt.setInt(1, bean.getId());
	 * ptmt.executeUpdate(); } catch (SQLException e){ e.printStackTrace(); }
	 * finally{ try{ // if (ptmt != null){ // ptmt.close(); // } if (connection
	 * != null){ connection.close(); } } catch (SQLException e){
	 * e.printStackTrace(); } catch (Exception e){ e.printStackTrace(); } } }
	 * 
	 * public AddonBean findByNameVersion(String name, String version) {
	 * 
	 * AddonBean bean = null; try{ String sql =
	 * "select * from addons where name=? and version=?"; connection =
	 * getConnection(); PreparedStatement ptmt =
	 * connection.prepareStatement(sql); ptmt.setString(1, name);
	 * ptmt.setString(2, version); ResultSet resultSet = ptmt.executeQuery();
	 * while (resultSet.next()){ bean = new AddonBean();
	 * bean.setId(resultSet.getInt("id"));
	 * bean.setSite_id(resultSet.getInt("site_id"));
	 * bean.setCategory_id(resultSet.getInt("category_id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setVersion(resultSet.getString("version"));
	 * bean.setDescription(resultSet.getString("description"));
	 * bean.setSource(resultSet.getString("source"));
	 * bean.setSize(resultSet.getInt("size"));
	 * bean.setChecksum(resultSet.getString("checksum")); } } catch
	 * (SQLException e){ e.printStackTrace(); } finally{ try{ // if (ptmt !=
	 * null){ // ptmt.close(); // } if (connection != null){ connection.close();
	 * } } catch (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } return bean; }
	 * 
	 * public AddonBean findById(int id) {
	 * 
	 * AddonBean bean = null; try{ String sql =
	 * "select * from addons where id=?"; connection = getConnection();
	 * PreparedStatement ptmt = connection.prepareStatement(sql); ptmt.setInt(1,
	 * id); ResultSet resultSet = ptmt.executeQuery(); while (resultSet.next()){
	 * bean = new AddonBean(); bean.setId(resultSet.getInt("id"));
	 * bean.setSite_id(resultSet.getInt("site_id"));
	 * bean.setCategory_id(resultSet.getInt("category_id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setVersion(resultSet.getString("version"));
	 * bean.setDescription(resultSet.getString("description"));
	 * bean.setSource(resultSet.getString("source"));
	 * bean.setSize(resultSet.getInt("size"));
	 * bean.setChecksum(resultSet.getString("checksum")); } } catch
	 * (SQLException e){ e.printStackTrace(); } finally{ try{ // if (ptmt !=
	 * null){ // ptmt.close(); // } if (connection != null){ connection.close();
	 * } } catch (SQLException e){ e.printStackTrace(); } catch (Exception e){
	 * e.printStackTrace(); } } return bean; }
	 * 
	 * public List<AddonBean> findByCategory(CategoryBean category_bean) {
	 * 
	 * List<AddonBean> list = new ArrayList<AddonBean>(); try{ String sql =
	 * "select * from addons where category_id=?"; connection = getConnection();
	 * PreparedStatement ptmt = connection.prepareStatement(sql); ptmt.setInt(1,
	 * category_bean.getId()); ResultSet resultSet = ptmt.executeQuery(); while
	 * (resultSet.next()){ AddonBean bean = new AddonBean();
	 * bean.setId(resultSet.getInt("id"));
	 * bean.setSite_id(resultSet.getInt("site_id"));
	 * bean.setCategory_id(resultSet.getInt("category_id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setVersion(resultSet.getString("version"));
	 * bean.setDescription(resultSet.getString("description"));
	 * bean.setSource(resultSet.getString("source"));
	 * bean.setSize(resultSet.getInt("size"));
	 * bean.setChecksum(resultSet.getString("checksum")); list.add(bean); } }
	 * catch (SQLException e){ e.printStackTrace(); } finally{ try{ // if (ptmt
	 * != null){ // ptmt.close(); // } if (connection != null){
	 * connection.close(); } } catch (SQLException e){ e.printStackTrace(); }
	 * catch (Exception e){ e.printStackTrace(); } } return list; }
	 * 
	 * public List<AddonBean> findAll() {
	 * 
	 * List<AddonBean> list = new ArrayList<AddonBean>(); try{ String sql =
	 * "select * from addons"; connection = getConnection(); PreparedStatement
	 * ptmt = connection.prepareStatement(sql); ResultSet resultSet =
	 * ptmt.executeQuery(); while (resultSet.next()){ AddonBean bean = new
	 * AddonBean(); bean.setId(resultSet.getInt("id"));
	 * bean.setSite_id(resultSet.getInt("site_id"));
	 * bean.setCategory_id(resultSet.getInt("category_id"));
	 * bean.setName(resultSet.getString("name"));
	 * bean.setVersion(resultSet.getString("version"));
	 * bean.setDescription(resultSet.getString("description"));
	 * bean.setSource(resultSet.getString("source"));
	 * bean.setSize(resultSet.getInt("size"));
	 * bean.setChecksum(resultSet.getString("checksum")); list.add(bean); } }
	 * catch (SQLException e){ e.printStackTrace(); } finally{ try{ // if (ptmt
	 * != null){ // ptmt.close(); // } if (connection != null){
	 * connection.close(); } } catch (SQLException e){ e.printStackTrace(); }
	 * catch (Exception e){ e.printStackTrace(); } } return list; }
	 */
}
